package org.wheel.plugins.toolkit.sql.driver;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.select.*;
import org.wheel.plugins.toolkit.sql.core.GlobalConfig;
import org.wheel.plugins.toolkit.sql.core.SqlSegment;
import org.wheel.plugins.toolkit.sql.enums.Dialect;
import org.wheel.plugins.toolkit.sql.enums.SqlFunction;
import org.wheel.plugins.toolkit.sql.helper.JSqlParseUtil;
import org.wheel.plugins.toolkit.sql.plugin.RulePlugin;
import org.wheel.plugins.toolkit.sql.segment.*;

import java.util.List;
import java.util.ServiceLoader;
import java.util.stream.Collectors;

/**
 * SegmentTree → JSqlParser AST → :name SQL
 */
public final class JsqlParserDriver {

    private final Dialect dialect;

    public JsqlParserDriver(Dialect dialect) {
        this.dialect = dialect;
    }

    /**
     * SQL 树转 SQL
     * 入口：SegmentTree → 含 :name 的 SQL 字符串
     * @param tree tree
     * @param isCountSql 是否是 count SQL
     * @return
     */
    public String toSql(SegmentTree tree, boolean isCountSql) {
        String with = "";
        boolean isWith = false;
        if(CollUtil.isNotEmpty(tree.getWithList())) {
            if(dialect.supportsWith()) {
                with = renderWith(tree, isCountSql);
                isWith = true;
            }else{
                throw new UnsupportedOperationException("不支持 WITH 语法");
            }
        }

        String mainSql = reandMain(tree, isCountSql, isWith);
        return with + mainSql;
    }

    private String reandMain(SegmentTree tree, boolean isCountSql, boolean isWith){
        PlainSelect select = new PlainSelect();

        // 如果是查询count，且没有分组函数
        if((CollUtil.isEmpty(tree.getGroupBySegments()) && tree.getHavingRoot() == null) && isCountSql){
            select.setSelectItems(CollUtil.newArrayList(new SelectItem<>(new Column("count(*)"), new Alias("count"))));
        }else {
            // 1. SELECT
            List<SelectItem<?>> items = CollUtil.newArrayList();
            for (SqlSegment seg : tree.getSelectList()) {
                // 简单实现：假设 SelectSegment 返回 "t2.amount AS workPackageMonthBOList.amount"
                String raw = seg.getSql();
                if (raw.equalsIgnoreCase(SqlFunction.DISTINCT.name())) {
                    Distinct distinct = new Distinct();
                    select.setDistinct(distinct);
                } else {
                    SelectItem<Column> item = new SelectItem<>();
                    item.setExpression(new Column(raw)); // 直接原样输出
                    items.add(item);
                }
            }
            select.setSelectItems(items);
        }
        // 2. FROM
        if(!isWith) {
            Table fromTable = new Table(tree.getMainTable())
                    .withAlias(new Alias(tree.getMainAlias(), true));
            select.setFromItem(fromTable);
        }else{
            select.setFromItem(new Table(tree.getWithFromTable())
                    .withAlias(new Alias(tree.getMainAlias(), true)));
        }

        // 3. JOIN
        for (JoinSegment j : tree.getJoinList()) {
            Join join = new Join();
            switch (j.getJoinType()){
                case INNER:
                    join.setInner(true);
                    break;
                case LEFT:
                    join.setLeft(true);
                    break;
                case RIGHT:
                    join.setRight(true);
                    break;
                case FULL:
                    join.setFull(true);
                    break;
                case CROSS:
                    join.setCross(true);
                    break;
                case OUTER:
                    join.setOuter(true);
                    break;
                default:
                    break;
            }
            join.setRightItem(new Table(j.getTable()).withAlias(new Alias(j.getAlias(), true)));
            join.setOnExpression(parseOn(j.getOnSegment()));
            select.addJoins(join);
        }

        // 4. WHERE
        if (CollUtil.isNotEmpty(tree.getWhereSegments())) {
            tree.buildWhereSql().ifPresent(whereRaw -> {
                Expression where = JSqlParseUtil.convertToExpression(whereRaw);
                select.setWhere(where);
            });
        }

        // 5. GROUP BY
        if (CollUtil.isNotEmpty(tree.getGroupBySegments())) {
            // 简单实现：假设返回字符串
            List<Column> groupList = tree.getGroupBySegments().stream()
                    .map(GroupBySegment::getSql)
                    .map(Column::new)
                    .collect(Collectors.toList());
            select.setGroupByElement(new GroupByElement()
                    .withGroupByExpressions(new ExpressionList<>(groupList)));
        }

        // 6. HAVING
        if (tree.getHavingRoot() != null) {
            Expression havingExpr = tree.getHavingRoot().toExpression();
            select.setHaving(havingExpr);
        }

        // 7. ORDER BY
        if (CollUtil.isNotEmpty(tree.getOrderBySegment()) && !isCountSql) {
            OrderByElement obe = new OrderByElement();
            obe.setExpression(new Column(CollUtil.join(tree.getOrderBySegment(), StrUtil.COMMA, OrderBySegment::getSql)));
            select.setOrderByElements(CollUtil.newArrayList(obe));
        }

        // 8. 规则插件
//        List<RulePlugin> rules = PluginLoader.getInstance().listRules();
        ServiceLoader<RulePlugin> rules = ServiceLoader.load(RulePlugin.class);
        for (RulePlugin p : rules) {
            if (tree.getRuleContext().isEnabled(p.name())) {
                p.apply(select, tree.getRuleContext());
            }
        }

        String sql = select.toString();
        if(tree.getPageSegment() != null && !isCountSql){
            sql = GlobalConfig.getDefaultPaginationStrategy()
                    .generatePaginationQuery(sql, tree.getPageSegment().getPageNo(), tree.getPageSegment().getPageSize());
        }

        // 10. 添加 count 因为第一个select判断了分组为空的情况，这里判断分组不为空，且需要计数 count 的情况
        if((CollUtil.isNotEmpty(tree.getGroupBySegments()) || tree.getHavingRoot() != null) && isCountSql){
            sql = StrUtil.format("select count(*) as count from ({}) tmp", sql) ;
        }

        // 9. 返回含 :name 的 SQL
        return sql;
    }


    private String renderWith(SegmentTree tree, boolean isCountSql) {
        if (!dialect.supportsWith() || tree.getWithList().isEmpty()) {
            return "";                 // 不支持或没写 WITH 直接返回空串
        }
        String startWith = "WITH " +
                tree.getWithList().stream()
                        .map(WithSegment::getSql)
                        .collect(Collectors.joining(", ")) + " ";
        return startWith;
    }
    /* ========== 简易解析工具 ========== */
    private static Expression parseOn(OnSegment on) {
        return JSqlParseUtil.convertToExpression(on.getSql());
    }
    private static Expression parseWhere(WhereSegment where) {
        return JSqlParseUtil.convertToExpression(where.getSql());
    }
    private static Expression parseExpression(String raw) {
        // 简易：直接当列
        return new Column(raw);
    }
}